package com.gxan.dao.impl.system;

import com.gxan.dao.system.SystemDao;
import com.gxan.pojo.*;
import com.gxan.utils.db.JdbcUtils;


import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class SystemDaoImpl extends JdbcUtils implements SystemDao {
    @Override
    public int addDepartment(String dept_full_name, String dept_short_name, String dept_father, String dept_address, String common, String remark, Integer start) throws SQLException {
        String sql = "INSERT INTO sys_dept(dept_full_name, dept_short_name, dept_father_id, dept_address, common, remark, start) \n" +
                "VALUES (?, ?, 1, ?, ?, ?, ?);";
        Object[] param = {dept_full_name, dept_short_name, dept_address, common, remark, start};

        return super.executeUpdate(sql, param);
    }

    //查询用户表所有用户信息
    @Override
    public List<SysUser> selectUSerAll(Integer param1, Integer param2) throws SQLException {
        String sql = "select user.user_id,user.login_name,user.`password`,user.role,dept.dept_full_name dept_name,user.phone,user.head_url  from sys_user user left join sys_dept dept on user.dept_id=dept.dept_id limit ?,?;";
        Object[] params = {param1, param2};
        List<SysUser> sysUsers = super.queryListEntity(SysUser.class, sql, params);
        return sysUsers;
    }

    //查询用户表用户数
    public Integer selectUserCount() throws SQLException {
        String sql = "select count(*) from sys_user;";
        return super.findAllForInt(sql);
    }

    //查询部门表中所有组织全称
    @Override
    public String[] selectDeptFullName() {
        return new String[0];
    }

    //通过用户名称，用户角色，所属部门查询用户信息
    @Override
    public List<SysUser> selectUserByNameRoleDep(String name, String role, String depart) {
        String sql = "select * from  sys_user,sys_dept where  sys_user.login_name=?\n" +
                "and sys_user.role=? and sys_user.dept_id=sys_dept.dept_id and sys_dept.dept_full_name=?;";
        Object[] param = {name, role, depart};
        return null;
    }

    //通过部门名查部门id
    @Override
    public int selectDepartByName(String name) throws SQLException {
        String sql = "select sys_dept.dept_id from sys_dept where sys_dept.dept_full_name=?;";
        Object[] param = {name};
        return super.findAllForInt(sql,param);
    }


    //新增用户
    @Override
    public int addUser(String login_name, Integer password, String role, Integer dept_id, Integer phone) throws SQLException {
        String sql = "INSERT INTO sys_user(login_name, password, role, dept_id, phone) \n" +
                "VALUES (?, ?, ?, ?, ?);";
        Object[] param = {login_name, password, role, dept_id, phone};
        return super.executeUpdate(sql, param);
    }

    @Override
    public int updateUser(Integer user_id, String login_name, String role, Integer dept_id, String phone) throws SQLException {
        String sql = "update sys_user";
        Object[] param = new Object[5];
        int count = -1;
        if (login_name != null && !"".equals(login_name.trim())) {
            count++;
            param[count] = login_name;
            if (count == 0) {
                sql += " set login_name=?";
            } else {
                sql += ",login_name=?";
            }

        }
        if (role != null && !"".equals(role.trim())) {
            count++;
            param[count] = role;
            if (count == 0) {
                sql += " set role=?";
            } else {
                sql += ",role=?";
            }

        }
        if (dept_id != null&&dept_id!=0) {
            count++;
            param[count] = dept_id;
            if (count == 0) {
                sql += " set dept_id=?";
            } else {
                sql += " , dept_id=?";
            }


        }
        if (phone != null&&!"".equals(phone.trim())) {
            count++;
            param[count] = phone;
            if (count == 0) {
                sql += " set phone=?";
            } else {
                sql += ",phone=?";
            }

        }
        if (count != -1) {
            param[++count] = user_id;
            sql += " where user_id=?";
        } else {
            return count;
        }
        System.out.println(sql);
        Object params[] = new Object[count+1];
        for (int i = 0,j=0; i < param.length; i++) {
            if (param[i]!=null&&!"".equals( param[i])){
                params[j++]=param[i];
            }
        }
        for (int i = 0; i < params.length; i++) {
            System.out.println(params[i]);
        }
        System.out.println(params.length);
       return super.executeUpdate(sql, params);
    }

    @Override
    public int delUserById(Integer id) throws SQLException {
        String sql = "delete from sys_user where user_id =?;";
        Object[] param = {id};
        return super.executeUpdate(sql, param);
    }

    @Override
    public List<SysUser> selectByNameRoleDepartment(String login_name, String role, String dept_name, Integer param01, Integer param02) throws SQLException {
        String sql = "select * from (select user.user_id,user.login_name,user.`password`,user.role,dept.dept_full_name dept_name,user.phone,user.head_url  from sys_user user left join sys_dept dept on user.dept_id=dept.dept_id)  a ";
//        where a.login_name='香香' and a.role='人事专员' and a.dept_name='Human Resources' limit 0,5;
//        Object[] param = new Object[5];
        int count = -1;
        if (login_name != null && !"".equals(login_name.trim())) {
            count++;
//            param[count] = login_name;
            if (count == 0) {
                sql += " where a.login_name like '%"+login_name+"%' ";
            } else {
                sql += " and a.login_name like '%"+login_name+"%' ";
            }

        }
        if (role != null && !"".equals(role.trim())) {
            count++;
//            param[count] = role;
            if (count == 0) {
                sql += " where a.role like '%"+role+"%' ";
            } else {
                sql += " and a.role like '%"+role+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
       sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        List<SysUser> sysUsers = super.queryListEntity(SysUser.class, sql, params);
        return sysUsers;
    }

    public Integer selectCountByNameRoleDepartment(String login_name, String role, String dept_name, Integer param01, Integer param02) throws SQLException {
        String sql = "select count(*) from (select user.user_id,user.login_name,user.`password`,user.role,dept.dept_full_name dept_name,user.phone,user.head_url  from sys_user user left join sys_dept dept on user.dept_id=dept.dept_id)  a ";
//        where a.login_name='香香' and a.role='人事专员' and a.dept_name='Human Resources' limit 0,5;
//        Object[] param = new Object[5];
        int count = -1;
        if (login_name != null && !"".equals(login_name.trim())) {
            count++;
//            param[count] = login_name;
            if (count == 0) {
                sql += " where a.login_name like '%"+login_name+"%' ";
            } else {
                sql += " and a.login_name like '%"+login_name+"%' ";
            }

        }
        if (role != null && !"".equals(role.trim())) {
            count++;
//            param[count] = role;
            if (count == 0) {
                sql += " where a.role like '%"+role+"%' ";
            } else {
                sql += " and a.role like '%"+role+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        int allForInt = super.findAllForInt(sql, params);
        return allForInt;
    }



    @Override
    public List<SysDept> selectAllDeptname() throws SQLException {
        String sql = "select distinct dept_full_name dept_name from sys_dept;";
        return super.queryListEntity(SysDept.class,sql);
    }

    //新增职位
    @Override
    public Integer addPosition(String job_name, String job_type, Integer dept_id, String description, String remark, Integer status) throws SQLException {
        String sql = "insert into sys_job(job_name,job_type,dept_id,description,remark,status) values(?,?,?,?,?,?);";
        Object[] param = {job_name, job_type, dept_id, description, remark,status};
        return super.executeUpdate(sql, param);
    }

    //查询职位表中的所有信息
    @Override
    public List<SysJob> selectAllPosition(Integer param01, Integer param02) throws SQLException {
        String sql = "select sys_job.job_id,sys_job.job_name,sys_job.job_type,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job  left join sys_dept dept on sys_job.dept_id=dept.dept_id limit ?,?;";
        Object[] params = {param01, param02};
        List<SysJob> sysUsers = super.queryListEntity(SysJob.class, sql, params);
        return sysUsers;
    }

    //查询职位表总条数
    @Override
    public Integer selectPositionCount() throws SQLException {
        String sql="select count(*) from sys_job;";
        return super.findAllForInt(sql);
    }

    //根据id删除职位表数据
    @Override
    public int deletePositionById(int id) throws SQLException {
        String sql = "delete from sys_job where job_id =?;";
        Object[] param = {id};
        return super.executeUpdate(sql, param);
    }

    @Override
    public int updatePositionById(Integer job_id, String job_name, String job_type, Integer dept_id, String description, String remark, Integer status) throws SQLException {
        String sql = "update sys_job";
        System.out.println("position-------dao");
        Object[] param = new Object[7];
        int count = -1;
//        job_name
        if (job_name != null && !"".equals(job_name.trim())) {
            count++;
            param[count] = job_name;
            if (count == 0) {
                sql += " set job_name=?";
            } else {
                sql += ",job_name=?";
            }

        }
//        job_type
        if (job_type != null && !"".equals(job_type.trim())) {
            count++;
            param[count] = job_type;
            if (count == 0) {
                sql += " set job_type=?";
            } else {
                sql += ",job_type=?";
            }

        }
//        dept_id
        if (dept_id != null&&dept_id!=0) {
            count++;
            param[count] = dept_id;
            if (count == 0) {
                sql += " set dept_id=?";
            } else {
                sql += " , dept_id=?";
            }


        }
//        description
        if ( description != null&&!"".equals(description.trim()) ) {
            count++;
            param[count] = description;
            if (count == 0) {
                sql += " set description=?";
            } else {
                sql += ",description=?";
            }

        }
//        remark
        if (remark != null && !"".equals(remark.trim())) {
            count++;
            param[count] = remark;
            if (count == 0) {
                sql += " set remark=?";
            } else {
                sql += ",remark=?";
            }

        }
//        status
        if (status != null) {
            count++;
            param[count] = status;
            if (count == 0) {
                sql += " set status=?";
            } else {
                sql += " , status=?";
            }


        }
//        job_id
        if (count != -1) {
            param[++count] = job_id;
            sql += " where job_id=?";
        } else {
            return count;
        }
        System.out.println(sql);
        Object params[] = new Object[count+1];
        for (int i = 0,j=0; i < param.length; i++) {
            if (param[i]!=null&&!"".equals( param[i])){
                params[j++]=param[i];
            }
        }
        for (int i = 0; i < params.length; i++) {
            System.out.println(params[i]);
        }
        System.out.println(params.length);
        return super.executeUpdate(sql, params);
    }


    @Override
    public Integer selectCountPositionByNameDepartmentStart(String job_name, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select count(*) from (select sys_job.job_id,sys_job.job_name,sys_job.`job_type`,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job left join sys_dept dept on sys_job.dept_id=dept.dept_id)  a ";
        Object[] param = new Object[5];
        int count = -1;
        if (job_name != null && !"".equals(job_name.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.job_name like '%"+job_name+"%' ";
            } else {
                sql += " and a.job_name like '%"+job_name+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null && status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        int allForInt = super.findAllForInt(sql, params);
        return allForInt;
    }

    @Override
    public List<SysJob> selectPositionByNameDepartmentStart(String job_name, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select * from (select sys_job.job_id,sys_job.job_name,sys_job.`job_type`,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job left join sys_dept dept on sys_job.dept_id=dept.dept_id)  a ";
        Object[] param = new Object[5];
        int count = -1;
        if (job_name != null && !"".equals(job_name.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.job_name like '%"+job_name+"%' ";
            } else {
                sql += " and a.job_name like '%"+job_name+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null&&status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        List<SysJob> sysUsers = super.queryListEntity(SysJob.class, sql, params);
        return sysUsers;
    }

    @Override
    public int addJobtitle(String job_title_name, int sysDepts, String description, String remark, Integer status) throws SQLException {
        String sql = "insert into sys_job_title(job_title_name,dept_id,description,remark,status) values(?,?,?,?,?);";
        Object[] param = {job_title_name,  sysDepts, description, remark,status};
        return super.executeUpdate(sql, param);
    }

    @Override
    public Integer selectJobtitleCount() throws SQLException {
        String sql="select count(*) from sys_job_title;";
        return super.findAllForInt(sql);
    }

    @Override
    public List<SysJobTitle> selectJobtitleAll(Integer param01, Integer param02) throws SQLException {
        String sql = "select sys_job.job_title_id,sys_job.job_title_name,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job_title sys_job  left join sys_dept dept on sys_job.dept_id=dept.dept_id limit ?,?;";
        Object[] params = {param01, param02};
        List<SysJobTitle> sysUsers = super.queryListEntity(SysJobTitle.class, sql, params);
        return sysUsers;
    }

    @Override
    public int deleteJobtitleById(int id) throws SQLException {
        String sql = "delete from sys_job_title where job_title_id =?;";
        Object[] param = {id};
        return super.executeUpdate(sql, param);
    }

    @Override
    public int updateJobtitleById(Integer job_title_id, String job_title_name, Integer dept_id, String description, String remark, Integer status) throws SQLException {
        String sql = "update sys_job_title";
        System.out.println("Jobtitle-------dao");
        Object[] param = new Object[6];
        int count = -1;
//        job_name
        if (job_title_name != null && !"".equals(job_title_name.trim())) {
            count++;
            param[count] = job_title_name;
            if (count == 0) {
                sql += " set job_title_name=?";
            } else {
                sql += ",job_title_name=?";
            }

        }

//        dept_id
        if (dept_id!=null&&dept_id!=0) {
            count++;
            param[count] = dept_id;
            if (count == 0) {
                sql += " set dept_id=?";
            } else {
                sql += " , dept_id=?";
            }


        }
//        description
        if ( description != null&&!"".equals(description.trim()) ) {
            count++;
            param[count] = description;
            if (count == 0) {
                sql += " set description=?";
            } else {
                sql += ",description=?";
            }

        }
//        remark
        if (remark != null && !"".equals(remark.trim())) {
            count++;
            param[count] = remark;
            if (count == 0) {
                sql += " set remark=?";
            } else {
                sql += ",remark=?";
            }

        }
//        status
        if (status != null&& status!=-1) {
            count++;
            param[count] = status;
            if (count == 0) {
                sql += " set status=?";
            } else {
                sql += " , status=?";
            }


        }
//        job_id
        if (count != -1) {
            param[++count] = job_title_id;
            sql += " where job_title_id=?";
        } else {
            return count;
        }
        System.out.println(sql);
        Object params[] = new Object[count+1];
        for (int i = 0,j=0; i < param.length; i++) {
            if (param[i]!=null&&!"".equals( param[i])){
                params[j++]=param[i];
            }
        }
        for (int i = 0; i < params.length; i++) {
            System.out.println(params[i]);
        }
        System.out.println(params.length);
        return super.executeUpdate(sql, params);
    }

    @Override
    public Integer selectCountJobtitleByNameDepartmentStatus(String job_title_name, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select count(*) from (select sys_job.job_title_id,sys_job.job_title_name,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job_title sys_job left join sys_dept dept on sys_job.dept_id=dept.dept_id)  a";
        int count = -1;
        if (job_title_name != null && !"".equals(job_title_name.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.job_title_name like '%"+job_title_name+"%' ";
            } else {
                sql += " and a.job_title_name like '%"+job_title_name+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null && status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        int allForInt = super.findAllForInt(sql, params);
        return allForInt;
    }

    @Override
    public List<SysJobTitle> selectJobtitleByNameDepartmentStatus(String job_title_name, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select * from (select sys_job.job_title_id,sys_job.job_title_name,dept.dept_full_name dept_name,sys_job.status,sys_job.description,sys_job.remark  from sys_job_title sys_job left join sys_dept dept on sys_job.dept_id=dept.dept_id)  a ";
        Object[] param = new Object[5];
        int count = -1;
        if (job_title_name != null && !"".equals(job_title_name.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.job_title_name like '%"+job_title_name+"%' ";
            } else {
                sql += " and a.job_title_name like '%"+job_title_name+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null&&status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
//        for (int i = 0,j=0; i < param.length; i++) {
//            if (param[i]!=null&&!"".equals( param[i])){
//                params[j++]=param[i];
//            }
//        }
//        for (int i = 0; i < params.length; i++) {
//            System.out.println(params[i]);
//        }
//        System.out.println(params.length);
        List<SysJobTitle> sysUsers = super.queryListEntity(SysJobTitle.class, sql, params);
        return sysUsers;
    }

    @Override
    public int addTest(String test_title, String test_type, Integer sysDepts, String test_level, String test_setter, Date date, String test_content, String remark, String test_url,Integer status) throws SQLException {
        String sql = "insert into test(test_title,test_type,dept_id,test_level,test_setter,test_set_time,status,test_content,remark,test_url) \n" +
                "values(?,?,?,?,?,?,?,?,?,?);";
        Object[] param = {test_title, test_type, sysDepts, test_level,test_setter,date,status,test_content,remark,test_url};
        return super.executeUpdate(sql, param);
    }

    @Override
    public Integer selectTestCount() throws SQLException {
        String sql="select count(*) from test;";
        return super.findAllForInt(sql);
    }

    @Override
    public List<Test> selectTestAll(Integer param01, Integer param02) throws SQLException {
        String sql = "select test.test_id,test.test_title,test.test_type,sys_dept.dept_full_name dept_name,test.test_level,test.test_setter,test.status,test.test_content,\n" +
                "test.remark,test.test_url from test left join sys_dept on test.dept_id=sys_dept.dept_id limit ?,?;";
        Object[] params = {param01, param02};
        List<Test> sysUsers = super.queryListEntity(Test.class, sql, params);
        return sysUsers;
    }

    @Override
    public int updateTestById(Integer test_id, String test_title, String test_type, Integer dept_id, String test_level, String test_setter, Date date, String test_content, String remark, String test_url, Integer status) throws SQLException {
        String sql = "update test";
        System.out.println("Jobtitle-------dao");
        Object[] param = new Object[11];
        int count = -1;
//        test_type
        if (test_type != null && !"".equals(test_type.trim())) {
            count++;
            param[count] = test_type;
            if (count == 0) {
                sql += " set test_type=?";
            } else {
                sql += ",test_type=?";
            }

        }
//        test_title
        if (test_title != null && !"".equals(test_title.trim())) {
            count++;
            param[count] = test_title;
            if (count == 0) {
                sql += " set test_title=?";
            } else {
                sql += ",test_title=?";
            }

        }
//        dept_id
        if (dept_id != null&& dept_id!=0) {
            count++;
            param[count] = dept_id;
            if (count == 0) {
                sql += " set dept_id=?";
            } else {
                sql += " , dept_id=?";
            }


        }
//        test_level
        if (test_level != null && !"".equals(test_level.trim())) {
            count++;
            param[count] = test_level;
            if (count == 0) {
                sql += " set test_level=?";
            } else {
                sql += ",test_level=?";
            }

        }
//        test_setter
        if (test_setter != null && !"".equals(test_setter.trim())) {
            count++;
            param[count] = test_setter;
            if (count == 0) {
                sql += " set test_setter=?";
            } else {
                sql += ",test_setter=?";
            }

        }
//        date
        if (date != null && !"".equals(date)) {
            count++;
            param[count] = date;
            if (count == 0) {
                sql += " set test_set_time=?";
            } else {
                sql += ",test_set_time=?";
            }

        }
//        test_content
        if (test_content != null && !"".equals(test_content.trim())) {
            count++;
            param[count] = test_content;
            if (count == 0) {
                sql += " set test_content=?";
            } else {
                sql += ",test_content=?";
            }

        }
//        test_url
        if (test_url != null && !"".equals(test_url.trim())) {
            count++;
            param[count] = test_url;
            if (count == 0) {
                sql += " set test_url=?";
            } else {
                sql += ",test_url=?";
            }

        }
//        remark
        if (remark != null && !"".equals(remark.trim())) {
            count++;
            param[count] = remark;
            if (count == 0) {
                sql += " set remark=?";
            } else {
                sql += ",remark=?";
            }

        }
//        status
        if (status != null&& status!=-1) {
            count++;
            param[count] = status;
            if (count == 0) {
                sql += " set status=?";
            } else {
                sql += " , status=?";
            }


        }
//        test_id
        if (count != -1) {
            param[++count] = test_id;
            sql += " where test_id=?";
        } else {
            return count;
        }

        System.out.println(sql);
        Object params[] = new Object[count+1];
        for (int i = 0,j=0; i < param.length; i++) {
            if (param[i]!=null&&!"".equals( param[i])){
                params[j++]=param[i];
            }
        }
        for (int i = 0; i < params.length; i++) {
            System.out.println(params[i]);
        }
        System.out.println(params.length);
        return super.executeUpdate(sql, params);
    }

    @Override
    public int deleteTestById(int id) throws SQLException {
        String sql = "delete from test where test_id =?;";
        Object[] param = {id};
        return super.executeUpdate(sql, param);
    }

    @Override
    public Integer selectTestCountByTitleTypeDepartmentStart(String test_title, String test_type, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select COUNT(*) from (select test.test_id,test.test_title,test.test_type,dept.dept_full_name dept_name,test.test_level,test.test_setter,test.test_set_time,test.status,test.test_content,test.remark,test.test_url  from test left join sys_dept dept on test.dept_id=dept.dept_id)  a ";
        Object[] param = new Object[5];
        int count = -1;
        if (test_title != null && !"".equals(test_title.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.test_title like '%"+test_title+"%' ";
            } else {
                sql += " and a.test_title like '%"+test_title+"%' ";
            }

        }
        if (test_type != null && !"".equals(test_type.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.test_type like '%"+test_type+"%' ";
            } else {
                sql += " and a.test_type like '%"+test_type+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null&&status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
        return super.findAllForInt(sql,params);
    }

    @Override
    public List<Test> selectTestByTitleTypeDepartmentStart(String test_title, String test_type, String dept_name, Integer status, Integer param01, Integer param02) throws SQLException {
        String sql = "select * from (select test.test_id,test.test_title,test.test_type,dept.dept_full_name dept_name,test.test_level,test.test_setter,test.test_set_time,test.status,test.test_content,test.remark,test.test_url  from test left join sys_dept dept on test.dept_id=dept.dept_id)  a ";
        Object[] param = new Object[5];
        int count = -1;
        if (test_title != null && !"".equals(test_title.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.test_title like '%"+test_title+"%' ";
            } else {
                sql += " and a.test_title like '%"+test_title+"%' ";
            }

        }
        if (test_type != null && !"".equals(test_type.trim())) {
            count++;
//            param[count] = job_name;
            if (count == 0) {
                sql += " where a.test_type like '%"+test_type+"%' ";
            } else {
                sql += " and a.test_type like '%"+test_type+"%' ";
            }

        }
        if (dept_name != null && !"".equals(dept_name.trim())) {
            count++;
//            param[count] = dept_name;
            if (count == 0) {
                sql += " where a.dept_name like '%"+dept_name+"%' ";
            } else {
                sql += " and a.dept_name like '%"+dept_name+"%' ";
            }

        }
        if (status != null&&status!=-1) {
            count++;
//            param[count] = status;
            if (count == 0) {
                sql += " where a.status like '%"+status+"%' ";
            } else {
                sql += " and a.status like '%"+status+"%' ";
            }
        }
//       param[++count]=param01; param[++count]=param02;
        sql+=" limit ?,?;";

        System.out.println(sql);
        Object params[] ={param01,param02};
        return super.queryListEntity(Test.class,sql,params);
    }


}

